﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTDetalleRep')
	BEGIN
		DROP  Procedure  OTDetalleRep
	END
GO


CREATE PROCEDURE [dbo].[OTDetalleRep]
	(	
		@EntregaId	AS INT
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 PuestoNombre	VARCHAR(50),
		 Horas			DECIMAL(10,2),
		 Total			MONEY)

	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'		AS TableName	UNION
	SELECT 2 AS TableId, 'OTDetalle'		AS TableName	UNION
	SELECT 3 AS TableId, 'OTMaterial'		AS TableName    UNION
	SELECT 4 AS TableId, 'OTGastoDirecto'	AS TableName    UNION
	SELECT 5 AS TableId, 'OTGastoIndirecto'	AS TableName    UNION
	SELECT 6 AS TableId, 'OTMaquina'		AS TableName   -- UNION		

	-- OrdenTrabajo
	SELECT o.NumeroOT, oe.Orden AS Entrega, e.Nombre AS EmpresaNombre, oe.FechaInicial, oe.FechaFinal, 
		   s.Estado, oe.UnidadesSolicitadas As Cantidad, oe.UnidadesProducidas AS CantidadProducida
	FROM Lit_OTEntrega oe
	INNER JOIN Lit_OT o ON oe.OTId = o.Id
	INNER JOIN Lit_Estado s ON oe.EstadoId = s.Id
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	WHERE oe.Id = @EntregaId

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, 
			SUM (ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, 
			SUM( (ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))
				+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) 
				+(ISNULL(HorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OTEntrega oe ON oe.Id = o.OTEntregaId
		INNER JOIN Lit_OT ot ON ot.Id = oe.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTEntregaId = @EntregaId		  
		GROUP BY ot.NumeroOT, p.Nombre
		UNION
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, 
			SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, 
			SUM( (ISNULL(HorasRegular,0) * ISNULL(CostoAyudantexHora,0))
				+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) 
				+(ISNULL(HorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OTEntrega oe ON oe.Id = o.OTEntregaId
		INNER JOIN Lit_OT ot ON ot.Id = oe.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTEntregaId = @EntregaId		  
		GROUP BY ot.NumeroOT, p.Nombre
	
	SELECT NumeroOT, PuestoNombre, SUM(Horas) AS Horas, SUM(Total) AS Total
	FROM @OTDetalle
	GROUP BY NumeroOT, PuestoNombre	

	-- OT Material
	SELECT ot.NumeroOT, oe.Orden AS Entrega, m.ArticuloId, art_nombre AS ArticuloNombre, m.Cantidad, CostoArticuloUnitario AS Costo, SUM(CostoArticuloTotal) AS Total
	FROM Lit_OTMaterial m	
	INNER JOIN Lit_OTEntrega oe ON oe.Id = m.OTEntregaId
	INNER JOIN Lit_OT ot ON ot.Id = oe.OTId
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id AND oe.EmpresaId = a.emp_id
	WHERE OTEntregaId = @EntregaId	
	GROUP BY ot.NumeroOT, oe.Orden, m.ArticuloId, art_nombre, m.Cantidad, CostoArticuloUnitario

	-- OT Gastos Directos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg	
	INNER JOIN Lit_OTEntrega oe ON oe.Id = otg.OTEntregaId	
	INNER JOIN Lit_OT ot ON ot.Id = oe.OTId	
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId	
	WHERE OTEntregaId = @EntregaId
	  AND g.Tipo = 'D'

	-- OT Gastos Indirectos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_OTEntrega oe ON oe.Id = otg.OTEntregaId	
	INNER JOIN Lit_OT ot ON ot.Id = oe.OTId
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId	
	WHERE OTEntregaId = @EntregaId AND g.Tipo = 'I'
	UNION
	SELECT NumeroOT, 0 As GastoId, 'Mano de Obra Indirecta' As GastoNombre, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OTEntrega oe
	INNER JOIN Lit_OT ot ON ot.Id = oe.OTId	
	WHERE oe.Id = @EntregaId	
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	-- OT Maquina
	SELECT ot.NumeroOT, o.MaquinaId, m.Nombre AS MaquinaNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, o.CostoMaquinaxHora, SUM(CostoMaquina) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_OTEntrega oe ON oe.Id = o.OTEntregaId	
	INNER JOIN Lit_OT ot ON ot.Id = oe.OTId	
	INNER JOIN Lit_Maquina m ON o.MaquinaId = m.Id
	WHERE OTEntregaId = @EntregaId  
	GROUP BY ot.NumeroOT, o.MaquinaId, m.Nombre, o.CostoMaquinaxHora
           	           
	SET NOCOUNT OFF
END
GO

